﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HCQ2_IDAL;
using HCQ2_Model.DocModel.HeadModel;
using HCQ2_Model.DocModel;
using System.Globalization;
using System.Data;
using HCQ2_Model.VR.Model;
using HCQ2_Model.VR.Params;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRHeadWordMainDAL: IT_VRHeadWordMainDAL
    {
        /// <summary>
        ///  获取单词检测记录列表
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <param name="total">记录总数</param>
        /// <returns></returns>
        public List<WordDetectionResult> GetWordDetectionList(WordDetectionParam param, out int total)
        {
            string str = $"SELECT TOP {param.rows} *  ";
            sb?.Clear();
            sb.Append($@" FROM (
            SELECT ROW_NUMBER() OVER(ORDER BY result.wh_id,result.wd)AS number,* FROM 
            (SELECT info.file_name,pass.cp_name,detail.*,users.* FROM 
            (SELECT wh_id,user_id,file_id,cp_id FROM T_VRHeadWordMain m)main INNER JOIN	
            (SELECT wh_id,wd,word,detletter,choletter,status,score,CONVERT(varchar(100),create_date,120) AS create_date,ISNULL(duration,0)AS duration FROM T_VRHeadWordDetailed WHERE 1=1 ");
            //单词
            if (!string.IsNullOrEmpty(param.word))
                sb.Append($" AND word LIKE '%{param.word}%' ");
            //时间
            string inDate = string.Empty, outDate = string.Empty;
            if (!string.IsNullOrEmpty(param.startDate))
                inDate = DateTime.ParseExact(param.startDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(param.endDate))
                outDate = DateTime.ParseExact(param.endDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                sb.AppendFormat($" AND create_date>='{inDate}' AND create_date<='{outDate}' ");
            else if (!string.IsNullOrEmpty(param.startDate) && string.IsNullOrEmpty(param.endDate))
                sb.AppendFormat($" AND create_date>='{inDate}' ");
            else if (string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                sb.AppendFormat($" AND create_date<='{outDate}' ");
            sb.Append($" )detail ON main.wh_id=detail.wh_id LEFT JOIN (SELECT file_id,file_name FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status=5 ");
            //课件名
            if (!string.IsNullOrEmpty(param.fileName))
                sb.Append($" AND file_name LIKE '%{param.fileName}%' ");
            sb.Append($" )info ON info.file_id = main.file_id LEFT JOIN (SELECT * FROM dbo.T_VRCustomPass WHERE 1=1 ");
            //关卡名
            if (!string.IsNullOrEmpty(param.cpName))
                sb.Append($" AND cp_name LIKE '%{param.cpName}%' ");
            sb.Append($" )pass ON pass.file_id = main.file_id AND pass.cp_id=main.cp_id  INNER JOIN (SELECT user_id,user_name FROM dbo.T_User WHERE 1=1 ");
            //用户名
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND user_name LIKE '%{param.keyword}%' ");
            sb.Append($" )users ON users.user_id = main.user_id)result)cp ");
            //数量
            string temp = "SELECT COUNT(0) AS num ";
            total = HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(temp + sb.ToString()));
            //记录
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(str + sb.ToString() + $" WHERE cp.number>{(param.page - 1) * param.rows} ORDER BY cp.create_date DESC;");
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<WordDetectionResult>(dt);
        }
        /// <summary>
        ///  获取单词体验排行榜数据
        /// </summary>
        /// <param name="param"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<WordRankResult> GetWordRankList(WordRankParam param, out int total)
        {
            string sqlHeader = $" SELECT TOP {param.rows} info.user_id,user_name=(SELECT user_name FROM dbo.T_User u WHERE info.user_id=u.user_id),info.score,info.date_len AS duration,CONVERT(INT,rank_order)AS rank_order FROM ";
            //方案一：一个人只有最高一条成绩
            //sb.Append($@" (SELECT ROW_NUMBER() OVER(ORDER BY main1.score DESC,main1.date_len ASC,main1.create_date ASC)AS rank_order,* FROM 
            // (SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY score DESC, date_len ASC, create_date ASC) AS number
            //  FROM dbo.T_VRHeadWordMain WHERE file_id = {param.file_id} AND cp_id = {param.cp_id}  {GetDateSql(param)}  ) main1 WHERE main1.number=1) info ");
            //方案二：一人允许有多条成绩排行
            sb.Append($@" (SELECT ROW_NUMBER() OVER(ORDER BY score DESC,date_len ASC,create_date ASC)AS rank_order,* FROM dbo.T_VRHeadWordMain WHERE file_id={param.file_id} AND cp_id={param.cp_id} {GetDateSql(param)} ) info ");
            //数量
            total = HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar("SELECT COUNT(0) AS num FROM "+sb.ToString()));
            StringBuilder sql = new StringBuilder();
            sql.Append(sqlHeader + sb.ToString() + $" WHERE info.rank_order >{ (param.page - 1) * param.rows} ORDER BY info.rank_order ASC;  ");           
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sql.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<WordRankResult>(dt);
        }
        private string GetDateSql(WordRankParam model)
        {
            string inDate = string.Empty, outDate = string.Empty;
            StringBuilder query = new StringBuilder();
            if (!string.IsNullOrEmpty(model.start_date))
                inDate = DateTime.ParseExact(model.start_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.end_date))
                outDate = DateTime.ParseExact(model.end_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.start_date) && !string.IsNullOrEmpty(model.end_date))
                query.AppendFormat($" AND create_date>='{inDate}' AND create_date<='{outDate}' ");
            else if (!string.IsNullOrEmpty(model.start_date) && string.IsNullOrEmpty(model.end_date))
                query.AppendFormat($" AND create_date>='{inDate}' ");
            else if (string.IsNullOrEmpty(model.start_date) && !string.IsNullOrEmpty(model.end_date))
                query.AppendFormat($" AND create_date<='{outDate}' ");
            else
                query.Append(" AND 1=1 ");
            return query.ToString();
        }
        /// <summary>
        ///  获取用户单词测试成绩列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<WordRankUserResult> GetWordRankByUserList(WordCoreByUser param,int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT ROW_NUMBER() OVER(ORDER BY score DESC,date_len ASC,create_date DESC)  AS number,wh_id,file_id,cp_id,CONVERT(varchar(100),create_date,120) AS create_date,date_len AS duration,score FROM dbo.T_VRHeadWordMain WHERE user_id={user_id} AND file_id={param.file_id} AND cp_id={param.cp_id} ORDER BY score DESC,duration ASC,create_date DESC;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<WordRankUserResult>(dt);
        }
        /// <summary>
        ///  获取自己最好成绩
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public WordRankResult GetMyWordRank(WordMydRankParam param)
        {
            sb.Append($@" SELECT info.user_id,user_name=(SELECT user_name FROM dbo.T_User u WHERE info.user_id=u.user_id),info.score,info.date_len AS duration,CONVERT(INT,rank_order)AS rank_order,
             num=(SELECT COUNT(0) AS num FROM T_VRHeadWordMain m WHERE m.user_id=info.user_id AND m.file_id=info.file_id AND m.cp_id=info.cp_id)FROM
             (SELECT ROW_NUMBER() OVER(ORDER BY main1.score DESC,main1.date_len ASC,main1.create_date ASC)AS rank_order,* FROM 
             (SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY score DESC, date_len ASC, create_date ASC) AS number
              FROM dbo.T_VRHeadWordMain WHERE file_id = {param.file_id} AND cp_id = {param.cp_id}) main1 WHERE main1.number=1) info 
              WHERE  user_id={param.user_id} ORDER BY info.rank_order ASC;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<WordRankResult>(dt)?.FirstOrDefault();
        }
        /// <summary>
        ///  获取测试详细记录列表
        /// </summary>
        /// <param name="wh_id"></param>
        /// <returns></returns>
        public List<HeadWordDetailedResult> GetMyDetectionList(int wh_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT detial.*,word.gj_symbol,word.china_mean FROM 
            (SELECT * FROM T_VRHeadWordMain WHERE wh_id={wh_id})main INNER JOIN
            (SELECT * FROM T_VRHeadWordDetailed WHERE wh_id={wh_id})detial ON detial.wh_id = main.wh_id INNER JOIN
            (SELECT * FROM T_VRCustomPassWord)word ON word.file_id = main.file_id AND word.cp_id = main.cp_id AND word.word = detial.word;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<HeadWordDetailedResult>(dt);
        }
    }
}